package com.ldj.hibernate;

import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;

import com.ldj.struts.form.EmployerInfoForm;

public class EmployerInfoDAO extends BaseHibernateDAO {
	public EmployerInfoDAO(HttpServletRequest request, HttpServletResponse response) {
		super(request, response);
		// TODO Auto-generated constructor stub
	}

	private static final Log log = LogFactory.getLog(EmployerInfoDAO.class);
	
	public List findAllPage(String code, EmployerInfoForm eForm, String scope) {
		try {
			String queryString = "from EmployerInfo where empInfoDelOper='0'";
			if(code != null && !"".equals(code))
				queryString += " and empInfoPlaceOrgan like '" + code + "%'";
			if(eForm.getEmpInfoShopCard() != null && !"".equals(eForm.getEmpInfoShopCard())){
				queryString += " and empInfoShopCard like '%" + eForm.getEmpInfoShopCard() + "%'";
			}
			if(eForm.getEmpInfoName() != null && !"".equals(eForm.getEmpInfoName())){
				queryString += " and empInfoName like '%" + eForm.getEmpInfoName()+ "%'";
			}
			if(eForm.getEmpInfoUnitType() != null && !"".equals(eForm.getEmpInfoUnitType())){
				queryString += " and empInfoUnitType='" + eForm.getEmpInfoUnitType()+ "'";
			}
			if(eForm.getEmpInfoCorporateRepresent() != null && !"".equals(eForm.getEmpInfoCorporateRepresent())){
				queryString += " and empInfoCorporateRepresent like '%" + eForm.getEmpInfoCorporateRepresent()+ "%'";
			}
			if(scope != null && !"".equals(scope))
				queryString += " and empInfoPlaceOrgan in ('" + scope + "')";
			Query queryObject = getSession().createQuery(queryString);
			this.setPageSize(8);
			return this.jlist(queryObject);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	
	
	//查找没有分配用户的单位
	public List findNotUserPage(String areaCode,EmployerInfoForm eForm) {
		try {
			String queryString = "from EmployerInfo where empInfoDelOper='0' and empInfoId not in(select unitUser from UnitUser where unitUser!=null) ";
			if(areaCode != null && !"".equals(areaCode))
				queryString += " and empInfoPlaceOrgan like '" + areaCode+ "%'";
			if(eForm.getEmpInfoName() != null && !"".equals(eForm.getEmpInfoName())){
				queryString += " and empInfoName like '%" + eForm.getEmpInfoName()+ "%'";
			}
			queryString += " order by empInfoId";
			Query queryObject = getSession().createQuery(queryString);
			this.setPageSize(8);
			return this.jlist(queryObject);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	//导入查询页
	public List findExcelAllPage(EmployerInfoForm eForm, String scope) {
		try {
			String queryString = "from EmployerInfo where empInfoDelOper='0'";
			if(eForm.getEmpInfoShopCard() != null && !"".equals(eForm.getEmpInfoShopCard())){
				queryString += " and empInfoShopCard like '%" + eForm.getEmpInfoShopCard() + "%'";
			}
			if(eForm.getEmpInfoName() != null && !"".equals(eForm.getEmpInfoName())){
				queryString += " and empInfoName like '%" + eForm.getEmpInfoName()+ "%'";
			}
			if(eForm.getEmpInfoUnitType() != null && !"".equals(eForm.getEmpInfoUnitType())){
				queryString += " and empInfoUnitType='" + eForm.getEmpInfoUnitType()+ "'";
			}
			if(eForm.getEmpInfoCorporateRepresent() != null && !"".equals(eForm.getEmpInfoCorporateRepresent())){
				queryString += " and empInfoCorporateRepresent like '%" + eForm.getEmpInfoCorporateRepresent()+ "%'";
			}
			if(scope != null && !"".equals(scope))
				queryString += " and empInfoPlaceOrgan in ('" + scope + "')";
			Query queryObject = getSession().createQuery(queryString);
			this.setPageSize(13);
			return this.jlist(queryObject);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	public List findNotUser() {
		try {
			String queryString = "from EmployerInfo where empInfoDelOper='0' and empInfoId not in(select unitUser from UnitUser where unitUser!=null) ";
			queryString += " order by empInfoId ";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	public List findAllApplyPage(String scorp, EmployerInfoForm eForm) {
		try {
			String queryString = "from EmployerInfo where empInfoDelOper='9' and empInfoPlaceOrgan in('" + scorp + "')";
			if(eForm.getEmpInfoShopCard() != null && !"".equals(eForm.getEmpInfoShopCard())){
				queryString += " and empInfoShopCard like '%" + eForm.getEmpInfoShopCard() + "%'";
			}
			if(eForm.getEmpInfoName() != null && !"".equals(eForm.getEmpInfoName())){
				queryString += " and empInfoName like '%" + eForm.getEmpInfoName()+ "%'";
			}
			if(eForm.getEmpInfoUnitType() != null && !"".equals(eForm.getEmpInfoUnitType())){
				queryString += " and empInfoUnitType='" + eForm.getEmpInfoUnitType()+ "'";
			}
			if(eForm.getEmpInfoCorporateRepresent() != null && !"".equals(eForm.getEmpInfoCorporateRepresent())){
				queryString += " and empInfoCorporateRepresent like '%" + eForm.getEmpInfoCorporateRepresent()+ "%'";
			}
			Query queryObject = getSession().createQuery(queryString);
			this.setPageSize(8);
			return this.jlist(queryObject);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	public EmployerInfo findById(java.lang.String id) {
		log.debug("getting EmployerInfo instance with id: " + id);
		try {
			EmployerInfo instance = (EmployerInfo) getSession().get(
					"com.ldj.hibernate.EmployerInfo", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}
	
	public void save(EmployerInfo transientInstance) {
		log.debug("saving EmployerInfo instance");
		try {
			getSession().save(transientInstance);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}
	
	public EmployerInfo merge(EmployerInfo detachedInstance) {
		try {
			EmployerInfo result = (EmployerInfo) getSession().merge(detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}
	
	public void delete(EmployerInfo persistentInstance) {
		log.debug("deleting EmployerInfo instance");
		try {
			getSession().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}
	
	public UnitUserApply findUnitUserApplyByEmpId(java.lang.String id) {
		log.debug("getting UnitUserApply instance with empid: " + id);
		try {
			String queryString = "from UnitUserApply where UUApplyState='0' and UUApplyUnitId=?";
			Query queryObject = getSession().createQuery(queryString);
			queryObject.setString(0, id);
			List list = queryObject.list();
			if(list != null && list.size() > 0)
				return (UnitUserApply)list.get(0);
			else
				return null;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}
	
	public UnitUser findUnitUserById(java.lang.String id) {
		log.debug("getting UnitUser instance with id: " + id);
		try {
			UnitUser instance = (UnitUser) getSession().get(
					"com.ldj.hibernate.UnitUser", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}
	
	public UnitUserApply merge(UnitUserApply detachedInstance) {
		try {
			UnitUserApply result = (UnitUserApply) getSession().merge(detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}
	
	public UnitUser merge(UnitUser detachedInstance) {
		try {
			UnitUser result = (UnitUser) getSession().merge(detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}
	
	public List findEmployer(String eid) {
		try {
			String queryString = "select empInfoId," +
					" empInfoName," +
					" empInfoBusinessRegisOrgan," +
					" empInfoShopCard," +
					" empInfoLandtaxRegisOrgan," +
					" empInfoBankOfDeposit," +
					" empInfoBankAccount," +
					" empInfoUnitType," +
					" empInfoIndustry," +
					" empInfoPlaceOrgan," +
					" empInfoPlaceAddress," +
					" empInfoCorporateRepresent," +
					" empInfoRepresentIdNum," +
					" empInfoCellPhone," +
					" empInfoTelphone," +
					" empInfoLabManaPrincipal," +
					" empInfoCellPhone1," +
					" empInfoTelphone1," +
					" empInfoManHourRegu," +
					" empInfoLowsalaLevel," +
					" empInfoAllWorkNum," +
					" empInfoTownWorkNum," +
					" empInfoCountryWorkNum," +
					" empInfoContractedNum," +
					" empInfoNcontractedNum," +
					" empInfoGoonContractNum," +
					" empInfoEmpInjInsNum," +
					" empInfoEndowmentInsNum," +
					" empInfoMedicalInsNum," +
					" empInfoUnempInsNum," +
					" empInfoMaterityInsNum," +
					" empInfoNoInsNum," +
					" empInfoIfOrgDealDisputed," +
					" empInfoIfContract," +
					" empInfoHonesLevel," +
					" empInfoDelOper," +
					" (select dataDictionaryName from DataDictionary dd where dd.dataDictionaryCode=ei.empInfoUnitType) as dname," +
					" (select count(*) from EmployerContractInfo ec where ec.employerInfo=ei.empInfoId and ec.empConContractTime='0' and ec.empConDelOper='0' and ec.empConContractEndTime>sysdate and ec.empConState='0') as num1," +
					" (select count(*) from EmployerContractInfo ec where ec.employerInfo=ei.empInfoId and ec.empConContractTime='1' and ec.empConDelOper='0' and ec.empConContractEndTime>sysdate and ec.empConState='0') as num2," +
					" (select count(*) from EmployerContractInfo ec where ec.employerInfo=ei.empInfoId and ec.empConContractTime='2' and ec.empConDelOper='0' and ec.empConContractEndTime>sysdate and ec.empConState='0') as num3," +
					" (select count(*) from EmployerContractInfo ec where ec.employerInfo=ei.empInfoId and ec.empConContractTime='3' and ec.empConDelOper='0' and ec.empConContractEndTime>sysdate and ec.empConState='0') as num4" +
					" from EmployerInfo ei where empInfoId='" + eid + "'";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	public List findEmployerHistoryList(String eid) {
		try {
			String queryString = "from EmployerHistory where empInfoId='" + eid + "' order by empHistoryYear";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	public List findEmployerContract(String eid) {
		try {
			String queryString = "select ec.empConId," +
			" ec.employerInfo," +
			" ec.empConEmpName," +
			" ec.empConEmpSex," +
			" ec.empConIdCardNum," +
			" ec.empConEducationLevel," +
			" ec.empConEmpDate," +
			" ec.empConContractTime," +
			" ec.empConContractBeginTime," +
			" ec.empConContractEndTime," +
			" ec.empConPost," +
			" ec.empConContractFileUrl," +
			" ec.empConState," +
			" ec.empConEndCouse," +
			" ec.empConRecontractendTime," +
			" ec.empConDelOper," +
			" (select dd.dataDictionaryName from DataDictionary dd where dd.dataDictionaryCode=ec.empConEducationLevel) as dname" +
			" from EmployerContractInfo ec where ec.employerInfo='" + eid + "' and ec.empConDelOper='0' and ec.empConContractEndTime>sysdate and ec.empConState='0'";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	public List findEndEmployerContract(String eid) {
		try {
			String queryString = "select ec.empConId," +
					" ec.employerInfo," +
					" ec.empConEmpName," +
					" ec.empConEmpSex," +
					" ec.empConIdCardNum," +
					" ec.empConEducationLevel," +
					" ec.empConEmpDate," +
					" ec.empConContractTime," +
					" ec.empConContractBeginTime," +
					" ec.empConContractEndTime," +
					" ec.empConPost," +
					" ec.empConContractFileUrl," +
					" ec.empConState," +
					" ec.empConEndCouse," +
					" ec.empConRecontractendTime," +
					" ec.empConDelOper," +
					" (select dd.dataDictionaryName from DataDictionary dd where dd.dataDictionaryCode=ec.empConEducationLevel) as dname" +
					" from EmployerContractInfo ec where ec.employerInfo='" + eid + "' and ec.empConDelOper='0' and ((ec.empConContractEndTime<=sysdate and ec.empConState='0') or ec.empConState='2')";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	public List findGoOnEmployerContract(String eid) {
		try {
			String queryString = "select ec.empConId," +
			" ec.employerInfo," +
			" ec.empConEmpName," +
			" ec.empConEmpSex," +
			" ec.empConIdCardNum," +
			" ec.empConEducationLevel," +
			" ec.empConEmpDate," +
			" ec.empConContractTime," +
			" ec.empConContractBeginTime," +
			" ec.empConContractEndTime," +
			" ec.empConPost," +
			" ec.empConContractFileUrl," +
			" ec.empConState," +
			" ec.empConEndCouse," +
			" ec.empConRecontractendTime," +
			" ec.empConDelOper," +
			" (select dd.dataDictionaryName from DataDictionary dd where dd.dataDictionaryCode=ec.empConEducationLevel) as dname" +
			" from EmployerContractInfo ec where ec.employerInfo='" + eid + "' and ec.empConDelOper='0' and ec.empConContractEndTime>sysdate and ec.empConState='0' and ec.empConContractType='1'";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	//根据单位名称查找单位
	 public EmployerInfo findByName(String name) {
			log.debug("getting EmployerInfo instance with name: " + name);
			try {
				String queryString = "from EmployerInfo where empInfoName = '" + name + "'";
				Query queryObject = getSession().createQuery(queryString);
				List list = queryObject.list();
				EmployerInfo	eiInfo =null;
				if(list !=null && list.size()>0){
				eiInfo = (EmployerInfo)list.get(0);
				}
				return eiInfo;
			} catch (RuntimeException re) {
				log.error("find all failed", re);
				throw re;
			}
		}
	 
	//查找所有的单位信息
	 public List findAll() {
			log.debug("getting EmployerInfo instance ");
			try {
				String queryString = "from EmployerInfo";
				Query queryObject = getSession().createQuery(queryString);
				List list = queryObject.list();
				return list;
			} catch (RuntimeException re) {
				log.error("find all failed", re);
				throw re;
			}
		}
}